package com.blog.dao.impl;

import com.blog.dao.ArticleDao;
import com.blog.dao.BaseDao;
import com.blog.pojo.Article;
import com.blog.utils.SqlIUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ArticleDaoImpl extends BaseDao implements ArticleDao {

    @Override
    public List<Article> getAllArticles() {
        List<Article> articleList = new ArrayList<Article>();
        try {
            String sql = "select * from tb_article";
            ResultSet resultSet = this.execQuery(sql, null);
            while (resultSet.next()) {
                int id = resultSet.getInt(1);
                String title = resultSet.getString(2);
                String author = resultSet.getString(3);
                String sort = resultSet.getString(4);
                String time = resultSet.getString(5);
                Integer star = resultSet.getInt(6);
                Integer comment = resultSet.getInt(7);
                Integer visit = resultSet.getInt(8);
                String content = resultSet.getString(9);
                Article article = new Article(id, title, author, sort, time, star, comment, visit, content);
                articleList.add(article);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConn(conn, resultSet, ps);
        }
        return articleList;
    }

    @Override
    public List<Article> getTop10Articles() {
        List<Article> articleList = new ArrayList<Article>();
        try {
            String sql = "select * from tb_article order by time desc limit 0,10";
            ResultSet resultSet = this.execQuery(sql, null);
            while (resultSet.next()) {
                int id = resultSet.getInt(1);
                String title = resultSet.getString(2);
                String author = resultSet.getString(3);
                String sort = resultSet.getString(4);
                String time = resultSet.getString(5);
                Integer star = resultSet.getInt(6);
                Integer comment = resultSet.getInt(7);
                Integer visit = resultSet.getInt(8);
                String content = resultSet.getString(9);
                Article article = new Article(id, title, author, sort, time, star, comment, visit, content);
                articleList.add(article);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConn(conn, resultSet, ps);
        }
        return articleList;
    }

    @Override
    public Map<String, Integer> getArticleSortAndCount() {
        Map<String, Integer> map = new HashMap<String, Integer>();
        try {
            String sql = "select sort,count(id) from tb_article GROUP BY sort";
            ResultSet resultSet = this.execQuery(sql, null);
            while (resultSet.next()) {
                String sort = resultSet.getString(1);
                Integer count = resultSet.getInt(2);
                map.put(sort, count);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConn(conn, resultSet, ps);
        }
        return map;
    }

    @Override
    public List<Article> getTop10VisitArticles() {
        List<Article> articleList = new ArrayList<Article>();
        try {
            String sql = "select * from tb_article order by visit desc limit 0,10";
            ResultSet resultSet = this.execQuery(sql, null);
            while (resultSet.next()) {
                int id = resultSet.getInt(1);
                String title = resultSet.getString(2);
                String author = resultSet.getString(3);
                String sort = resultSet.getString(4);
                String time = resultSet.getString(5);
                Integer star = resultSet.getInt(6);
                Integer comment = resultSet.getInt(7);
                Integer visit = resultSet.getInt(8);
                String content = resultSet.getString(9);
                Article article = new Article(id, title, author, sort, time, star, comment, visit, content);
                articleList.add(article);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConn(conn, resultSet, ps);
        }
        return articleList;
    }

    @Override
    public List<Article> getArticlesBySort(String sort) {
        List<Article> articleList = new ArrayList<Article>();
        if(SqlIUtils.isSqlValid(sort)){
            return articleList;
        }
        try {
            String sql = "select * from tb_article where sort=?";
            Object[] parms = {sort};
            ResultSet resultSet = this.execQuery(sql, parms);
            while (resultSet.next()) {
                int id = resultSet.getInt(1);
                String title = resultSet.getString(2);
                String author = resultSet.getString(3);
                sort = resultSet.getString(4);
                String time = resultSet.getString(5);
                Integer star = resultSet.getInt(6);
                Integer comment = resultSet.getInt(7);
                Integer visit = resultSet.getInt(8);
                String content = resultSet.getString(9);
                Article article = new Article(id, title, author, sort, time, star, comment, visit, content);
                articleList.add(article);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConn(conn, resultSet, ps);
        }
        return articleList;
    }

    @Override
    public List<Article> getArticlesByTagName(String tagName) {
        List<Article> articleList = new ArrayList<Article>();
        if(SqlIUtils.isSqlValid(tagName)){
            return articleList;
        }
        try {
            String sql = "select tb_article.* from tb_article INNER JOIN tb_tag on  tb_article.id=tb_tag.id where tb_tag.tag=?";
            Object[] parms = {tagName};
            ResultSet resultSet = this.execQuery(sql, parms);
            while (resultSet.next()) {
                int id = resultSet.getInt(1);
                String title = resultSet.getString(2);
                String author = resultSet.getString(3);
                String sort = resultSet.getString(4);
                String time = resultSet.getString(5);
                Integer star = resultSet.getInt(6);
                Integer comment = resultSet.getInt(7);
                Integer visit = resultSet.getInt(8);
                String content = resultSet.getString(9);
                Article article = new Article(id, title, author, sort, time, star, comment, visit, content);
                articleList.add(article);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConn(conn, resultSet, ps);
        }
        return articleList;
    }

    @Override
    public List<String> getArticlesYears() {
        List<String> years = new ArrayList<String>();
        try {
            //原sql语句存在错误，去掉distinct后可执行，但需要后续添加的去重操作
            String sql="SELECT YEAR ( time ) FROM tb_article ORDER BY tb_article.time DESC";
            ResultSet resultSet = this.execQuery(sql, null);
            while (resultSet.next()) {
                String year = resultSet.getString(1);
                if(!years.contains(year)){
                    years.add(year);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            this.closeConn(conn, resultSet, ps);
        }
        return years;
    }

    @Override
    public List<Article> getArticlesByYears(String year) {
        List<Article> articleList = new ArrayList<Article>();
        if(SqlIUtils.isSqlValid(year)){
            return articleList;
        }
        try {
            String sql="select * from tb_article where YEAR(time)=? order by time desc";
            Object[] parms = {year};
            ResultSet resultSet = this.execQuery(sql, parms);
            while (resultSet.next()) {
                int id = resultSet.getInt(1);
                String title = resultSet.getString(2);
                String author = resultSet.getString(3);
                String sort = resultSet.getString(4);
                String time = resultSet.getString(5);
                Integer star = resultSet.getInt(6);
                Integer comment = resultSet.getInt(7);
                Integer visit = resultSet.getInt(8);
                String content = resultSet.getString(9);
                Article article = new Article(id, title, author, sort, time, star, comment, visit, content);
                articleList.add(article);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConn(conn, resultSet, ps);
        }
        return articleList;
    }

    @Override
    public Article getById(String id) {
        Article article = null;
        if(SqlIUtils.isSqlValid(id)){
            return article;
        }
        try {
            String sql = "select * from tb_article where id=?";
            ResultSet resultSet = this.execQuery(sql, new Object[]{id});
            while (resultSet.next()) {
                String title = resultSet.getString(2);
                String author = resultSet.getString(3);
                String sort = resultSet.getString(4);
                String time = resultSet.getString(5);
                Integer star = resultSet.getInt(6);
                Integer comment = resultSet.getInt(7);
                Integer visit = resultSet.getInt(8);
                String content = resultSet.getString(9);
                article = new Article(Integer.valueOf(id), title, author, sort, time, star, comment, visit, content);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConn(conn, resultSet, ps);
        }
        return article;
    }

    @Override
    public Article getPreArticle(Integer id) {
        Article article = null;
        try {
            String sql = "select * from tb_article where id<? order by id desc limit 0,1";
            ResultSet resultSet = this.execQuery(sql, new Object[]{id});
            while (resultSet.next()) {
                String title = resultSet.getString(2);
                String author = resultSet.getString(3);
                String sort = resultSet.getString(4);
                String time = resultSet.getString(5);
                Integer star = resultSet.getInt(6);
                Integer comment = resultSet.getInt(7);
                Integer visit = resultSet.getInt(8);
                String content = resultSet.getString(9);
                Integer article_id = resultSet.getInt(1);
//                article = new Article(Integer.valueOf(id), title, author, sort, time, star, comment, visit, content);
                //这里的id设置就是导致上下文翻页失败的原因
                article = new Article(Integer.valueOf(article_id), title, author, sort, time, star, comment, visit, content);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConn(conn, resultSet, ps);
        }
        return article;
    }

    @Override
    public Article getNextArticle(Integer id) {
        Article article = null;
        try {
            String sql = "select * from tb_article where id>? order by id asc limit 0,1";
            ResultSet resultSet = this.execQuery(sql, new Object[]{id});
            while (resultSet.next()) {
                String title = resultSet.getString(2);
                String author = resultSet.getString(3);
                String sort = resultSet.getString(4);
                String time = resultSet.getString(5);
                Integer star = resultSet.getInt(6);
                Integer comment = resultSet.getInt(7);
                Integer visit = resultSet.getInt(8);
                String content = resultSet.getString(9);
                Integer article_id = resultSet.getInt(1);
//                article = new Article(Integer.valueOf(id), title, author, sort, time, star, comment, visit, content);
                //这里的id设置就是导致上下文翻页失败的原因
                article = new Article(Integer.valueOf(article_id), title, author, sort, time, star, comment, visit, content);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConn(conn, resultSet, ps);
        }
        return article;
    }

    @Override
    public int updateVisitCount(String id) {
        if(SqlIUtils.isSqlValid(id)){
            return 0;
        }
        String sql="update tb_article set visit=visit+1 where id=?";
        return this.execUpdate(sql, new Object[]{id});
    }

    @Override
    public int add(Article article) {
        if(SqlIUtils.isSqlValid(article.getContent()+" "+article.getTime()+" "+article.getAuthor()+" "+article.getTitle()+" "+article.getSort())){
            return 0;
        }
        String sql = "insert into tb_article values (null,?,?,?,?,?,?,?,?)";
        Object[] parms = {article.getTitle(), article.getAuthor(), article.getSort(), article.getTime(), article.getStar(), article.getComment(), article.getVisit(), article.getContent()};
        return this.execUpdate(sql, parms);
    }

    @Override
    public Article getLastedArticle() {
        Article article = null;
        try {
            String sql = "select * from tb_article order by id desc limit 0,1";
            ResultSet resultSet = this.execQuery(sql, null);
            while (resultSet.next()) {
                int articleId = resultSet.getInt(1);
                String title = resultSet.getString(2);
                String author = resultSet.getString(3);
                String sort = resultSet.getString(4);
                String time = resultSet.getString(5);
                int star = resultSet.getInt(6);
                int comment = resultSet.getInt(7);
                int visit = resultSet.getInt(8);
                String content = resultSet.getString(9);
                article = new Article(articleId, title, author, sort, time, star, comment, visit, content);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConn(conn, resultSet, ps);
        }
        return article;
    }

    @Override
    public int deleteById(String articleId) {
        if(SqlIUtils.isSqlValid(articleId)){
            return 0;
        }
        String sql = "delete from tb_article where id=?";
        Object[] parms = {articleId};
        return this.execUpdate(sql, parms);
    }

    @Override
    public int deleteSort(String sortName) {
        if(SqlIUtils.isSqlValid(sortName)){
            return 0;
        }
        String sql = "delete from tb_article where sort=?";
        Object[] parms = {sortName};
        return this.execUpdate(sql, parms);
    }

    @Override
    public int addStar(String articleId) {
        if(SqlIUtils.isSqlValid(articleId)){
            return 0;
        }
        String sql="update tb_article set star=star+1 where id=?";
        return this.execUpdate(sql, new Object[]{articleId});
    }

    @Override
    public int updateCommentCount(String mark, String id) {
        if(SqlIUtils.isSqlValid(mark+" "+id)){
            return 0;
        }
        String sql1="update tb_article set comment=comment+1 where id=?";
        String sql2="update tb_article set comment=comment-1 where id=?";
        if(mark.equalsIgnoreCase("+"))
        {
            return this.execUpdate(sql1, new Object[]{id});
        }else{
            return this.execUpdate(sql2, new Object[]{id});
        }

    }

    @Override
    public int edit(Article article) {
        if(SqlIUtils.isSqlValid(article.getContent()+" "+article.getTime()+" "+article.getAuthor()+" "+article.getTitle()+" "+article.getSort())){
            return 0;
        }
        String sql="update tb_article set title=?,author=?,sort=?,time=?,star=?,comment=?,visit=?,content=? where id=?";
        Object[]parms={article.getTitle(),article.getAuthor(),article.getSort(),article.getTime(),article.getStar(),article.getComment(),article.getVisit(),article.getContent(),article.getId()};
        return this.execUpdate(sql,parms);



    }

    @Override
    public int editSort(String sortName, String newSort) {
        if(SqlIUtils.isSqlValid(sortName+" "+newSort)){
            return 0;
        }
        String sql="update tb_article set sort=? where sort=?";
        Object[]parms={newSort,sortName};

        return this.execUpdate(sql,parms);
    }
}
